package com.samebuilding.dao.impl;


import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.samebuilding.dao.CommentReplyDAO;
import com.samebuilding.domain.CommentReply;

public class CommentReplyDAOImpl extends GenericDAOImpl<CommentReply, Integer>
		implements CommentReplyDAO {

	private static final String DELETE_COMMENTREPLY_BY_COMMENTID = "delete from CommentReply where commentId = ?";
	private static final String GET_COUNT_UNREAD_COMMENT = "select count(cr.id) from CommentReply cr, Comment c where cr.readed = 0 and cr.commentId = c.id and c.receiveUserNo = ? and cr.replyUserNo != ?";
	private static final String GET_COUNT_UNREAD_COMMENTREPLY = "select count(cr.id) from CommentReply cr, Comment c where cr.readed = 0 and cr.commentId = c.id and c.sendUserNo = ? and cr.replyUserNo != ?";
	private static final String UPDATE_COMMENT_UNREAD_TO_READ = "update CommentReply cr, Comment c set cr.readed = 1 where cr.commentId = c.id and c.receiveUserNo = ?";
	private static final String UPDATE_COMMENTREPLY_UNREAD_TO_READ = "update CommentReply cr, Comment c set cr.readed = 1 where cr.commentId = c.id and c.sendUserNo = ?";
	
	
	
	public void deleteCommentReplyByCommentId(final Integer commentId) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(DELETE_COMMENTREPLY_BY_COMMENTID);
				query.setInteger(0, commentId);
				query.executeUpdate();
				return null;
			}
		});
		
	}

	public int getCountUnreadComment(final Integer receiveUserNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_UNREAD_COMMENT);
				query.setInteger(0, receiveUserNo);
				query.setInteger(1, receiveUserNo);
				
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public int getCountUnreadCommentReply(final Integer leaveUserNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_UNREAD_COMMENTREPLY);
				query.setInteger(0, leaveUserNo);
				query.setInteger(1, leaveUserNo);
				
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public void updateCommentReplyUnreadToRead(final Integer leaveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(UPDATE_COMMENTREPLY_UNREAD_TO_READ);
				query.setInteger(0, leaveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	public void updateCommentUnreadToRead(final Integer receiveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(UPDATE_COMMENT_UNREAD_TO_READ);
				query.setInteger(0, receiveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

}
